Grafana Dashboard Documentation

This document provides an overview of the Prometheus queries used in the Grafana dashboard(DB Monitoring with Exporter) for monitoring PostgreSQL metrics via postgresql_exporter.

Queries

1. Version: Retrieves static information about the PostgreSQL instance.

   pg_static{release="$release", instance="$instance"}
   • Variables:
     • $release: The release version of the PostgreSQL instance.
     • $instance: The specific instance of PostgreSQL being monitored.

2. Current Status: Fetches the Current Status of PostgreSQL instance.

   pg_up{release="$release", instance="$instance"}

3. Current Fetch Data: Sums the number of tuples fetched from the database.

   SUM(pg_stat_database_tup_fetched{datname=~"$datname", instance=~"$instance"})

4. Current Insert Data: Sums the number of tuples inserted into the database.

   SUM(pg_stat_database_tup_inserted{release="$release", datname=~"$datname", instance=~"$instance"})

5. Current Update Data: Sums the number of tuples updated in the database.

   SUM(pg_stat_database_tup_updated{datname=~"$datname", instance=~"$instance"})

6. Max Connections: Retrieves the maximum number of connections allowed for the PostgreSQL instance.

   pg_settings_max_connections{release="$release", instance="$instance"}

7. Average CPU usage: Calculates the average CPU usage in milliseconds over a 5-minute window.

   avg(rate(process_cpu_seconds_total{release="$release", instance="$instance"}[5m]) * 1000)

8. Average Memory usage: Calculates the average resident memory usage over a 5-minute window.

   avg(rate(process_resident_memory_bytes{release="$release", instance="$instance"}[5m]))

9. Open File Descriptors: Retrieves the number of open file descriptors for the PostgreSQL process. Monitoring this metric is important because it helps ensure that PostgreSQL process is not running out of file descriptors, which could lead to performance issues or failures.

   process_open_fds{release="$release", instance="$instance"}

10. Shared Buffers (Bytes): Displays the amount of memory allocated for shared buffers in PostgreSQL. Monitoring this metric is crucial for understanding how much memory is being used for caching data, which can significantly impact database performance. Properly configured shared buffers can improve query performance by reducing disk I/O.

    pg_settings_shared_buffers_bytes{instance="$instance"}

11. Effective Cache Size (Bytes): Displays the estimated amount of memory available for disk caching by the operating system and within the database itself. Monitoring this metric helps in understanding how much memory PostgreSQL assumes is available for caching data, which can influence query planning and performance. Properly setting this parameter can lead to more efficient query execution plans.

    pg_settings_effective_cache_size_bytes{instance="$instance"}

12. Maintenance Work Memory (Bytes): Displays the amount of memory allocated for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE. Monitoring this metric helps ensure that sufficient memory is available for these operations, which can improve their performance.

    pg_settings_maintenance_work_mem_bytes{instance="$instance"}

13. Work Memory (Bytes): Shows the amount of memory allocated for internal sort operations and hash tables before writing to temporary disk files. Properly setting this parameter can improve the performance of complex queries that require sorting or hashing.

    pg_settings_work_mem_bytes{instance="$instance"}

14. Max WAL Size (Bytes): Indicates the maximum size of the Write-Ahead Logging (WAL) files. Monitoring this metric helps in understanding the disk space requirements for WAL files and ensuring that the system does not run out of disk space, which could lead to database downtime.

    pg_settings_max_wal_size_bytes{instance="$instance"}

15. Random Page Cost: Represents the estimated cost of a non-sequentially fetched disk page. This parameter influences the query planner's decision-making process. Monitoring this metric helps in tuning the database for better performance, especially for workloads with random I/O patterns.

    pg_settings_random_page_cost{instance="$instance"}

16. Sequential Page Cost: Represents the estimated cost of a sequentially fetched disk page. Like the random page cost, this parameter influences the query planner's decisions. Properly setting this parameter can help optimize query performance for sequential scans.

    pg_settings_seq_page_cost

17. Max Worker Processes: Displays the maximum number of background processes that PostgreSQL can run. Monitoring this metric helps ensure that there are enough worker processes available for parallel operations and other background tasks.

    pg_settings_max_worker_processes{instance="$instance"}

18. Max Parallel Workers: Indicates the maximum number of parallel workers that can be used for parallel queries. Properly setting this parameter can improve the performance of parallel queries by allowing more workers to be used.

    pg_settings_max_parallel_workers{instance="$instance"}

19. Active Sessions Count: Retrieves the number of active sessions in the PostgreSQL database. Monitoring this metric helps in understanding the current load on the database and identifying potential performance bottlenecks.

pg_stat_activity_count{datname=~"$datname", instance=~"$instance", state="active"} !=0

20. Transaction Commit and Rollback Rate: Measures the rate of transactions being committed and rolled back in the PostgreSQL database over a 5-minute interval. This metric is crucial for assessing the database's transaction throughput and identifying any anomalies in transaction processing.

irate(pg_stat_database_xact_commit{instance="$instance", datname=~"$datname"}[5m])
irate(pg_stat_database_xact_rollback{instance="$instance", datname=~"$datname"}[5m])

21. Updated Tuples Count: Retrieves the number of tuples (rows) updated in the PostgreSQL database. Monitoring this metric helps in understanding the frequency of updates and can be useful for performance tuning and identifying potential issues with data modification operations.

pg_stat_database_tup_updated{datname=~"$datname", instance=~"$instance"} != 0

22. Fetched Tuples Count: Retrieves the number of tuples (rows) fetched from the PostgreSQL database. Monitoring this metric helps in understanding the read activity on the database and can be useful for performance tuning and identifying potential issues with data retrieval operations.

pg_stat_database_tup_fetched{datname=~"$datname", instance=~"$instance"} != 0

23. Inserted Tuples Count: Retrieves the number of tuples (rows) inserted into the PostgreSQL database. Monitoring this metric helps in understanding the write activity on the database and can be useful for performance tuning and identifying potential issues with data insertion operations.

pg_stat_database_tup_inserted{datname=~"$datname", instance=~"$instance"} != 0

24. Locks Count: Retrieves the number of locks held in the PostgreSQL database. Monitoring this metric helps in understanding the locking behavior and can be useful for identifying potential issues with database concurrency and performance.

pg_locks_count{datname=~"$datname", instance=~"$instance", mode=~"$mode"} != 0

25. Returned Tuples Count: Retrieves the number of tuples (rows) returned by queries in the PostgreSQL database. Monitoring this metric helps in understanding the read activity and can be useful for performance tuning and identifying potential issues with data retrieval operations.

pg_stat_database_tup_returned{datname=~"$datname", instance=~"$instance"} != 0

26. Idle Sessions Count: Retrieves the number of sessions in the PostgreSQL database that are idle, idle in transaction, or idle in transaction (aborted). Monitoring this metric helps in understanding session behavior and can be useful for identifying potential issues with session management and database performance.

pg_stat_activity_count{datname=~"$datname", instance=~"$instance", state=~"idle|idle in transaction|idle in transaction (aborted)"}

27. Deleted Tuples Count: Retrieves the number of tuples (rows) deleted from the PostgreSQL database. Monitoring this metric helps in understanding the delete activity and can be useful for performance tuning and identifying potential issues with data deletion operations.

pg_stat_database_tup_deleted{datname=~"$datname", instance=~"$instance"} != 0

28. Cache Hit Ratio: Calculates the ratio of blocks found in the PostgreSQL buffer cache to the total number of blocks read. Monitoring this metric helps in understanding the efficiency of the database cache and can be useful for performance tuning and identifying potential issues with disk I/O.

pg_stat_database_blks_hit{instance="$instance", datname=~"$datname"} / (pg_stat_database_blks_read{instance="$instance", datname=~"$datname"} + pg_stat_database_blks_hit{instance=~"$instance", datname=~"$datname"})

29. Buffers (bgwriter): This provides insights into the performance of the PostgreSQL background writer using various Prometheus metrics

    irate(pg_stat_bgwriter_buffers_backend{instance="$instance"}[5m])
    irate(pg_stat_bgwriter_buffers_alloc{instance="$instance"}[5m])
    irate(pg_stat_bgwriter_buffers_backend_fsync{instance="$instance"}[5m])
    irate(pg_stat_bgwriter_buffers_checkpoint{instance="$instance"}[5m])
    irate(pg_stat_bgwriter_buffers_clean{instance="$instance"}[5m])

30. Database Conflicts and Deadlocks Rate: Measures the rate of conflicts and deadlocks occurring in the PostgreSQL database over a 5-minute interval. Monitoring these metrics helps in understanding the contention and concurrency issues within the database, which can be useful for performance tuning and identifying potential problems with transaction management.

   irate(pg_stat_database_conflicts{instance="$instance", datname=~"$datname"}[5m])
   irate(pg_stat_database_deadlocks{instance="$instance", datname=~"$datname"}[5m])

Conclusion

These queries provide essential metrics for monitoring the performance, resource usage and status of PostgreSQL instances. Adjust the variables as needed to customize the dashboard for different environments or instances.